<?php
class report_model extends jqxGrid_CI_Model {

    function __construct()
    {
        parent::__construct();
        
    }
    function gettotal_rows_synthesis_month($startdate,$enddate){
        $configs["strQuery"]="
            SELECT
                dtb_product.ProductName AS ProductName,
                dtb_order_details.ProductID AS ProductID,
                dtb_order_details.UnitPrice AS UnitPrice,
                Sum(dtb_order_details.Quantity) AS TotalQuantity,
                dtb_order_details.Discount AS Discount,
                Sum((((`dtb_order_details`.`UnitPrice` * `dtb_order_details`.`Quantity`) * (100 - `dtb_order_details`.`Discount`)) / 100)) AS TotalOrderCash,
                dtb_orders.OrderID AS OrderID,
                dtb_orders.OrderDate AS OrderDate,
                dtb_orders.Status AS Status,
                dtb_product.Supplier AS Supplier,
                dtb_product.Amount AS Amount,
                dtb_product.Sold AS Sold
            FROM (
                    (`dtb_order_details` 
                    join `dtb_orders` 
                    on((`dtb_order_details`.`OrderID` = `dtb_orders`.`OrderID`))
                    ) join `dtb_product` 
                    on((`dtb_order_details`.`ProductID` = `dtb_product`.`ProductID`))
                )
            

            ";
        $configs["strWhere"]="
            WHERE
                'Finished' =  dtb_orders.Status
                AND ((`OrderDate` BETWEEN '$startdate' and '$enddate') OR (`OrderDate` BETWEEN '$enddate' and '$startdate'))
            ";
        $configs["strGroupBy"]="
            GROUP BY
                dtb_order_details.ProductID";
        $configs["usinglimit"]=false;
        $this->init($configs);
        return $this->jqxBinding();
    }
    function Line_Series_Data_Month($startdate,$enddate){
        $query="
            SELECT
                dview_order_report.OrderDate,
                ROUND(Sum(dview_order_report.OrderCash)) AS TotalCash
            FROM
                dview_order_report
            WHERE
                dview_order_report.OrderDate BETWEEN  '$startdate' AND '$enddate'
                AND dview_order_report.Status='Finished'
            GROUP BY
                dview_order_report.OrderDate
            ";
        $query = $this->db->query($query);
        return $query->result();
    }
    function Chart_Gray_Scale_Month($startdate,$enddate){
        $query="
            SELECT
                d.OrderDate,
                (select ROUND(Sum(d1.OrderCash)) 
                    FROM
                        dview_order_report d1
                    WHERE
                        d1.OrderDate =d.OrderDate
                        AND d1.Status='Finished'
                ) AS TotalCash,
                (select ROUND(Sum(d2.OrderCash))
                    FROM
                        dview_order_report d2
                    WHERE
                        d2.OrderDate =d.OrderDate
                        AND d2.Status<>'Finished'
                        AND d2.Status<>'New'
                        AND d2.Status<>'Canceled'
                ) AS TotalCashUnFinish
            FROM
                dview_order_report d
            WHERE
                d.OrderDate BETWEEN  '$startdate' AND '$enddate'
            GROUP BY
                d.OrderDate
            ";
        $query = $this->db->query($query);
        return $query->result();
    }
    function Chart_Gray_Scale_Year($startdate,$enddate){
        $query="
            SELECT
                month(d.OrderDate) as Month,
                (select ROUND(Sum(d1.OrderCash)) 
                    FROM
                        dview_order_report d1
                    WHERE
                        month(d1.OrderDate) =month(d.OrderDate)
                        AND d1.Status='Finished'
                ) AS TotalCash,
                (select ROUND(Sum(d2.OrderCash))
                    FROM
                        dview_order_report d2
                    WHERE
                        month(d2.OrderDate) =month(d.OrderDate)
                        AND d2.Status<>'Finished'
                        AND d2.Status<>'New'
                        AND d2.Status<>'Canceled'
                ) AS TotalCashUnFinish
            FROM
                dview_order_report d
            WHERE
                d.OrderDate BETWEEN  '$startdate' AND '$enddate'
            GROUP BY
                month(d.OrderDate)
            ";
        $query = $this->db->query($query);
        return $query->result();
    }
    function Line_Series_Symbol_Data_Year($startdate,$enddate){
        $query="
            SELECT
                month(dview_order_report.OrderDate) as Month,
                ROUND(Sum(dview_order_report.OrderCash)) AS TotalCash
            FROM
                dview_order_report
            WHERE
                dview_order_report.OrderDate BETWEEN  '$startdate' AND '$enddate'
                AND dview_order_report.Status='Finished'
            GROUP BY
                month(dview_order_report.OrderDate)
            ";
        $query = $this->db->query($query);
        return $query->result();
    }
    function Chart_Donut_Year($year){
        $query="
            SELECT
                CEILING(month(dview_order_report.OrderDate)/3) as Quater,
                ROUND(Sum(dview_order_report.OrderCash)) AS TotalCash
            FROM
                dview_order_report
            WHERE
                year(dview_order_report.OrderDate)='$year'
                AND dview_order_report.Status='Finished'
            GROUP BY
                CEILING(month(dview_order_report.OrderDate)/3)
            ";
        $query = $this->db->query($query);
        return $query->result();
    }
    
}
?>
